﻿Imports myBookmarks.Core
Imports System.Data.Linq

Namespace myBookmarks.Data
    Public Class BookmarkRepository
        Implements IBookmarkRepository

        Private _db As BookmarkContext

        Public Sub New(ByVal dataContext As BookmarkContext)
            _db = dataContext
        End Sub

#Region " Bookmark Specific"

        Public Function GetTotalCount(ByVal username As String) As Integer
            Return (From b In _db.bm_Bookmarks _
                   Where b.UserName = username).Count
        End Function

        Public Function GetBookmarkById(ByVal id As Integer) As Bookmark Implements IBookmarkRepository.GetBookmarkById
            Return (From b In _db.bm_Bookmarks _
                                 Where b.BookmarkId = id _
                                 Order By b.Name Ascending _
                                 Select New Bookmark With {.BookmarkId = b.BookmarkId, _
                                                          .CreatedDate = b.CreatedDate, _
                                                          .Labels = (From la In _db.bm_Labels _
                                                                    Join lb In _db.bm_BookmarkLabels On la.LabelId Equals lb.LabelId _
                                                                    Where lb.BookmarkId = b.BookmarkId _
                                                                    Order By la.LabelName _
                                                                    Select New Label With {.LabelName = la.LabelName, .LabelId = la.LabelId, .UserName = la.UserName}).ToList, _
                                                          .LastUpdated = b.LastUpdated, _
                                                          .Location = b.Location, _
                                                          .Name = b.Name, _
                                                          .UserName = b.UserName, _
                                                          .Notes = b.Notes}).SingleOrDefault
        End Function

        Public Function GetBookmarks(ByVal userName As String, ByVal sortBy As SortBy, ByVal start As Integer, ByVal max As Integer, ByRef total As Integer) As IEnumerable(Of BookmarkList) Implements IBookmarkRepository.GetBookmarks
            Dim var As IQueryable(Of BookmarkList)
            Select Case sortBy

                Case sortBy.Label
                    var = From b In GetBookmarks(userName) _
                           Order By b.Labelnames Ascending _
                           Select b
                Case sortBy.DateCreated
                    var = From b In GetBookmarks(userName) _
                           Order By b.CreatedDate Descending _
                                Select b
                Case Else
                    var = From b In GetBookmarks(userName) _
                          Order By b.Name Ascending _
                               Select b
            End Select
            total = var.Count
            Return var.Skip(start).Take(max).ToList
        End Function

        Public Function GetBookmarksByLabel(ByVal userName As String, ByVal label As String, ByVal sortBy As SortBy, ByVal start As Integer, ByVal max As Integer, ByRef total As Integer) As IEnumerable(Of BookmarkList) Implements IBookmarkRepository.GetBookmarksByLabel
            Dim var As IQueryable(Of BookmarkList)
            Select Case sortBy

                Case sortBy.Label
                    var = From b In GetBookmarks(userName) _
                           Where b.Labelnames.ToLower.Contains(label.ToLower) _
                           Order By b.Labelnames Ascending _
                                Select b
                Case sortBy.DateCreated
                    var = From b In GetBookmarks(userName) _
                           Where b.Labelnames.ToLower.Contains(label.ToLower) _
                           Order By b.CreatedDate Descending _
                                Select b
                Case Else
                    var = From b In GetBookmarks(userName) _
                       Where b.Labelnames.ToLower.Contains(label.ToLower) _
                        Order By b.Name Ascending _
                           Select b
            End Select
            total = var.Count
            Return var.Skip(start).Take(max).ToList
        End Function

        Public Function GetBookmarksByLocation(ByVal userName As String, ByVal location As String) As Bookmark Implements IBookmarkRepository.GetBookmarksByLocation
            Return (From b In _db.bm_Bookmarks _
                                 Where b.Location.ToLower = location.ToLower _
                                 And b.UserName = userName _
                                 Order By b.Name Ascending _
                                 Select New Bookmark With {.BookmarkId = b.BookmarkId, _
                                                          .CreatedDate = b.CreatedDate, _
                                                          .Labels = (From la In _db.bm_Labels _
                                                                    Join lb In _db.bm_BookmarkLabels On la.LabelId Equals lb.LabelId _
                                                                    Where lb.BookmarkId = b.BookmarkId _
                                                                    Order By la.LabelName _
                                                                    Select New Label With {.LabelName = la.LabelName, .LabelId = la.LabelId, .UserName = la.UserName}).ToList, _
                                                          .LastUpdated = b.LastUpdated, _
                                                          .Location = b.Location, _
                                                          .Name = b.Name, _
                                                          .UserName = b.UserName, _
                                                          .Notes = b.Notes}).SingleOrDefault
        End Function

        Public Function SearchBookmarks(ByVal userName As String, ByVal search As String, ByVal sortBy As SortBy, ByVal start As Integer, ByVal max As Integer, ByRef total As Integer) As IEnumerable(Of BookmarkList) Implements IBookmarkRepository.SearchBookmarks
            Dim var As IQueryable(Of BookmarkList)
            Select Case sortBy

                Case sortBy.Label
                    var = From b In GetBookmarks(userName) _
                                 Where b.Location.Contains(search) Or b.Name.Contains(search) Or b.Notes.Contains(search) Or b.Labelnames.Contains(search) _
                                         Order By b.Labelnames Ascending _
                                      Select b
                Case sortBy.DateCreated
                    var = From b In GetBookmarks(userName) _
                                 Where b.Location.Contains(search) Or b.Name.Contains(search) Or b.Notes.Contains(search) Or b.Labelnames.Contains(search) _
                                         Order By b.CreatedDate Descending _
                                      Select b
                Case Else
                    var = From b In GetBookmarks(userName) _
                                Where b.Location.Contains(search) Or b.Name.Contains(search) Or b.Notes.Contains(search) Or b.Labelnames.Contains(search) _
                                        Order By b.Name Ascending _
                                     Select b

            End Select
            total = var.Count
            Return var.Skip(start).Take(max).ToList
        End Function

        Public Function SaveBookmark(ByVal bookmark As Bookmark) As Bookmark Implements IBookmarkRepository.SaveBookmark
            Dim bookmarkToSave As bm_Bookmark = Nothing
            Try

                If bookmark.BookmarkId <> 0 Then
                    bookmarkToSave = (From bo In _db.bm_Bookmarks _
                    Where bo.BookmarkId = bookmark.BookmarkId).FirstOrDefault
                End If

                If bookmarkToSave Is Nothing Then
                    bookmarkToSave = (From bo In _db.bm_Bookmarks _
                                      Where bo.Location.ToLower = bookmark.Location.ToLower).FirstOrDefault
                    If bookmarkToSave Is Nothing Then
                        bookmarkToSave = New bm_Bookmark
                        bookmarkToSave.CreatedDate = DateTime.Now
                        _db.bm_Bookmarks.InsertOnSubmit(bookmarkToSave)
                    End If
                End If
                With bookmarkToSave
                    .Location = bookmark.Location
                    .LastUpdated = DateTime.Now
                    .Name = bookmark.Name
                    .Notes = bookmark.Notes
                    .UserName = bookmark.UserName
                End With

                For Each l As Label In bookmark.Labels

                    Dim persistenceLabel As bm_Label = _db.bm_Labels.Where(Function(lb As bm_Label) lb.LabelName.ToLower = l.LabelName.ToLower And lb.UserName = bookmarkToSave.UserName).FirstOrDefault
                    If persistenceLabel Is Nothing Then
                        'create the label if the label is not in the database for the user
                        persistenceLabel = New bm_Label With {.LabelName = l.LabelName, .UserName = bookmarkToSave.UserName}
                        bookmarkToSave.bm_BookmarkLabels.Add(New bm_BookmarkLabel With {.bm_Bookmark = bookmarkToSave, .bm_Label = persistenceLabel})
                    End If
                    'Relate the label with the bookmarks
                    If Not (persistenceLabel.LabelId = 0 OrElse _db.bm_BookmarkLabels.Where(Function(bl As bm_BookmarkLabel) bl.BookmarkId = bookmarkToSave.BookmarkId AndAlso bl.LabelId = persistenceLabel.LabelId).Any()) Then
                        bookmarkToSave.bm_BookmarkLabels.Add(New bm_BookmarkLabel With {.bm_Bookmark = bookmarkToSave, .bm_Label = persistenceLabel})
                    End If
                Next


                Dim updatedLabelNames = bookmark.Labels.Select(Function(l As Label) l.LabelName.ToLower)

                'Remove the other labels for the bookmark
                Dim labelsRemoved = From l In _db.bm_Labels _
                 Join bl In _db.bm_BookmarkLabels On l.LabelId Equals bl.LabelId _
                 Where bl.BookmarkId = bookmarkToSave.BookmarkId AndAlso Not updatedLabelNames.Contains(l.LabelName.ToLower()) _
                 Select bl
                _db.bm_BookmarkLabels.DeleteAllOnSubmit(labelsRemoved)



                _db.SubmitChanges()
            Catch ex As Exception
                Throw ex
            End Try
            'Remove any labels that dont have a bookmark
            RemoveUnusedLabels(bookmark.UserName)
            Return GetBookmarkById(bookmarkToSave.BookmarkId)

        End Function


        Public Function DeleteBookmark(ByVal bookmark As Bookmark) As Boolean Implements IBookmarkRepository.DeleteBookmark

            Try
                Dim b As bm_Bookmark = (From bm In _db.bm_Bookmarks _
                               Where bm.BookmarkId = bookmark.BookmarkId _
                                   Select bm).SingleOrDefault

                _db.bm_Bookmarks.DeleteOnSubmit(b)

                _db.SubmitChanges()

            Catch ex As Exception
                Throw ex
            End Try
            RemoveUnusedLabels(bookmark.UserName)
            Return True
        End Function

        Public Function DeleteAllBookmarks(ByVal username As String) As Boolean Implements IBookmarkRepository.DeleteAllBookmarks
            Try
                Dim b = (From bm In _db.bm_Bookmarks _
                               Where bm.UserName = username _
                                   Select bm)
                _db.bm_Bookmarks.DeleteAllOnSubmit(b)


                _db.SubmitChanges()
            Catch ex As Exception
                Throw ex
            End Try
            RemoveUnusedLabels(username)
            Return False
        End Function


        Public Function GetBookmarks(ByVal username As String) As IQueryable(Of BookmarkList)
            Return ((From b In _db.bm_BookmarkViews _
                                 Where b.username = username _
                                 Order By b.name Ascending _
                                 Select New BookmarkList With {.BookmarkId = b.bookmarkid, _
                                                          .CreatedDate = b.createddate, _
                                                          .Labelnames = b.labelname, _
                                                          .LastUpdated = b.lastupdated, _
                                                          .Location = b.location, _
                                                          .Name = b.name, _
                                                          .UserName = b.username, _
                                                          .Notes = b.notes}))
        End Function
#End Region

#Region " Label Specific"


        Public Function GetLabels(ByVal username As String) As IEnumerable(Of Label) Implements IBookmarkRepository.GetLabels
            Return (From la In _db.bm_Labels _
                   Where la.UserName = username _
                   Select New Label With {.LabelName = la.LabelName, .LabelId = la.LabelId, .UserName = la.UserName}).ToList
        End Function

        Public Function GetLabelsWithBookmarkCount(ByVal username As String) As IEnumerable(Of KeyValuePair(Of String, Integer)) Implements IBookmarkRepository.GetLabelsWithBookmarkCount

            Return (From lb In _db.bm_LabelCountViews _
                   Where lb.UserName = username _
                   Select New KeyValuePair(Of String, Integer)(lb.LabelName, lb.BookmarkCount)).ToList

        End Function

        Public Sub SaveLabel(ByVal label As Label) Implements IBookmarkRepository.SaveLabel

            Dim lb As bm_Label = (From la In _db.bm_Labels _
                                 Where la.LabelId = label.LabelId _
                                 Select la).SingleOrDefault
            lb.LabelName = label.LabelName
            _db.SubmitChanges()
        End Sub


        Public Function RemoveLabel(ByVal id As Integer) As Boolean Implements IBookmarkRepository.RemoveLabel

            Try
                Dim lb As bm_Label = (From la In _db.bm_Labels _
                               Where la.LabelId = id _
                               Select la).SingleOrDefault
                _db.bm_Labels.DeleteOnSubmit(lb)

                _db.SubmitChanges()
            Catch ex As Exception
                Throw ex
            End Try

            Return False
        End Function

        Sub RemoveUnusedLabels(ByVal username As String)

            Dim removeLabels = (From l In _db.bm_Labels _
                                                 Where l.UserName = username And l.bm_BookmarkLabels.Count = 0 _
                                                 Select l)
            _db.bm_Labels.DeleteAllOnSubmit(removeLabels)
            _db.SubmitChanges()

        End Sub

        Public Sub RemoveLabels(ByVal username As String)
            Dim removeLabels = (From l In _db.bm_Labels _
                                   Where l.UserName = username And l.bm_BookmarkLabels.Count = 0 _
                                   Select l).ToList
            _db.bm_Labels.DeleteAllOnSubmit(removeLabels)
            _db.SubmitChanges()

        End Sub

        Function AddLabels(ByVal labels As List(Of Label)) As List(Of bm_Label)
            Dim labelList As New List(Of bm_Label)
            For Each la In labels

                Dim lab As bm_Label = (From labe In _db.bm_Labels _
                                    Where labe.LabelName.ToLower = la.LabelName.ToLower _
                                    Select labe).SingleOrDefault
                If lab Is Nothing Then
                    lab = New bm_Label With {.LabelName = la.LabelName, .UserName = la.UserName}
                    _db.bm_Labels.InsertOnSubmit(lab)
                End If

                labelList.Add(lab)
            Next
            _db.SubmitChanges()
            Return labelList
        End Function
#End Region

    End Class

End Namespace
